﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Dynamics.AX.Framework.Tools.ModelManagement;

namespace AxUtilUI
{
    public partial class FMain : Form
    {
        AxUtilCalls axUtilCalls;
        List<string> databases = new List<string>();
        private int sortColumn = 0;
        private IList<ModelElementInfo> ModelElements = null;

        private void LogInfo(string s, ExecutionStatus status = ExecutionStatus.Ok)
        {

            rtbInfoLog.SelectionStart = rtbInfoLog.Text.Length;
            switch (status)
            {
                case ExecutionStatus.Warning:
                    rtbInfoLog.SelectionColor = Color.Orange;
                    break;
                case ExecutionStatus.Error:
                    rtbInfoLog.SelectionColor = Color.Red;
                    break;
                default:
                    rtbInfoLog.SelectionColor = SystemColors.ControlText;
                    break;
            }
            rtbInfoLog.AppendText(string.Format("\n{0} > {1}", DateTime.Now, s));
            rtbInfoLog.SelectionLength = s.Length;
            rtbInfoLog.SelectionStart = rtbInfoLog.Text.Length;
            rtbInfoLog.ScrollToCaret();
            rtbInfoLog.Invalidate();
            rtbInfoLog.Invalidate();
            rtbInfoLog.Refresh();
        }

        public FMain()
        {
            InitializeComponent();
            //Font = SystemFonts.MenuFont;
            txtDbNameMask.Text = Properties.Settings.Default.DatabaseFilter;
            cbxAxLayer.DataSource = System.Enum.GetValues(typeof(Layer));
            cbxAxLayer.SelectedIndex = cbxAxLayer.FindString(Layer.Isv.ToString());
            cbxModelDeletionPrevention.Checked = true;
            cbxModelConflicts.SelectedIndex = 0;
            AxUtilConfiguration config = new AxUtilConfiguration();
            AxUtilContext context = new AxUtilContext();
            config.CopyFromAOSRegistry(context);
            cbxSqlServerName.Items.Clear();
            if (!string.IsNullOrEmpty(Properties.Settings.Default.SqlServer))
            {
                cbxSqlServerName.Items.Add(Properties.Settings.Default.SqlServer);
            }
            else if (!string.IsNullOrEmpty(config.Server))
            {
                cbxSqlServerName.Items.Add(config.Server);
            }
            if (cbxSqlServerName.Items.Count > 0)
            {
                cbxSqlServerName.SelectedIndex = 0;
            }

            //cbxSqlDbName.Text = axUtilConfiguration.Database;
            try
            {
                RefreshDatabases();
            }
            catch { }
            if (!string.IsNullOrEmpty(Properties.Settings.Default.SqlModelDatabase))
            {
                cbxSqlDbName.Text = Properties.Settings.Default.SqlModelDatabase;
            }

            List<System.Reflection.Assembly> assemblies = AppDomain.CurrentDomain.GetAssemblies().ToList();
            foreach (System.Reflection.Assembly assembly in assemblies)
            {
                if (assembly.GetName().Name == "AXUtilLib")
                {
                    rtbInfoLog.AppendText(string.Format("Dynamics AX 2012 AxUtil UI Log (AxUtilLib version: {0}):", System.Diagnostics.FileVersionInfo.GetVersionInfo(assembly.Location).FileVersion));
                    break;
                }
            }

            RefreshLocalAosInfo();
        }

        private static string CreateConnectionString(string server, string database)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder connStrBuild = new System.Data.SqlClient.SqlConnectionStringBuilder();
            connStrBuild.DataSource = server;
            connStrBuild.InitialCatalog = database;
            connStrBuild.IntegratedSecurity = true;
            connStrBuild.PersistSecurityInfo = false;
            connStrBuild.MultipleActiveResultSets = true;
            connStrBuild.Pooling = false;
            connStrBuild.ConnectTimeout = 10;
            return connStrBuild.ToString();
        } 

        private List<string> EnumerateSqlDatabases(string sqlServer)
        {
            List<string> databases = new List<string>();
            using (System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(CreateConnectionString(sqlServer, string.Empty)))
            {
                SqlCon.Open();
                System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                SqlCom.Connection = SqlCon;
                SqlCom.CommandType = CommandType.Text;
                SqlCom.CommandText = "select name from sys.databases where state_desc='ONLINE' order by 1";

                System.Data.SqlClient.SqlDataReader SqlDR;
                SqlDR = SqlCom.ExecuteReader();

                while (SqlDR.Read())
                {
                    string dbName = SqlDR.GetString(0);
                    databases.Add(dbName);
                }
            }
            return databases;
        }

        private void btnListModels_Click(object sender, EventArgs e)
        {
            if (axUtilCalls == null)
            {
                MessageBox.Show("Select a modelstore first", "No modelstore", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            lvwModels.Items.Clear();
            AxUtilContext context;
            IList<ModelManifest> models = axUtilCalls.ListModels(out context);

            if (ProcessContextSuccesful(context))
            {
                if (models != null)
                {
                    foreach (ModelManifest model in models)
                    {
                        ListViewItem item = lvwModels.Items.Add(string.Empty);
                        item.SubItems.AddRange(new string[] { model.ModelId.ToString(), model.Layer.ToString(), model.Name, model.Publisher, model.Version, model.Signed.ToString(), model.Description });
                        item.Tag = model;
                    }
                }
            }
        }

        private bool ProcessContextSuccesful(AxUtilContext context)
        {
            switch (context.ExecutionStatus)
            {
                case ExecutionStatus.Warning:
                    if (context.Warnings != null)
                    {
                        foreach (string text in context.Warnings)
                        {
                            LogInfo(text, ExecutionStatus.Warning);
                        }
                    }
                    return true;
                case ExecutionStatus.Error:
                    if (context.Errors != null)
                    {
                        foreach (string text in context.Errors)
                        {
                            LogInfo(text, ExecutionStatus.Error);
                        }
                    }
                    return false;
                default:
                    return true;
            }
        }

        private void btnSelectModelExportPath_Click(object sender, EventArgs e)
        {
            folderBrowserDlg.Description = "Select the default export folder:";
            if (folderBrowserDlg.ShowDialog() == DialogResult.OK)
            {
                cbxModelDefaultExportPath.Text = folderBrowserDlg.SelectedPath;
            }
        }

        private void btnExportSelectedModels_Click(object sender, EventArgs e)
        {
            if (System.IO.Directory.Exists(cbxModelDefaultExportPath.Text))
            {
                List<ModelManifest> modelsToExport = new List<ModelManifest>();
                string modelList = string.Empty;
                foreach (ListViewItem item in lvwModels.Items)
                {
                    if (!item.Checked)
                        continue;
                    ModelManifest modelMan = item.Tag as ModelManifest;
                    if (modelMan.ModelId.HasValue)
                    {
                        modelsToExport.Add(modelMan);
                        modelList += string.Format("- id={0}, version={1}, name={2}\n", modelMan.ModelId.Value, modelMan.Version, modelMan.Name);
                    }
                }

                if (MessageBox.Show(
                            string.Format("The following models will be exported:\n{0}\nAre you sure?", modelList),
                            "Are you sure?",
                            MessageBoxButtons.YesNo,
                            MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Yes)
                {
                    foreach (ModelManifest model in modelsToExport)
                    {
                        string exportFile = string.Format(@"{0}\{1}_{2}.axmodel", cbxModelDefaultExportPath.Text.TrimEnd('\\'), model.Layer.Value, model.Name.Replace(' ', '_'));
                        AxUtilContext context;
                        LogInfo(string.Format("Exporting model id={0}, layer={1}, name={2}, version={3}, publisher={4} to \"{5}\"", model.ModelId.Value, model.Layer.Value, model.Name, model.Version, model.Publisher, exportFile), ExecutionStatus.Ok);
                        rtbInfoLog.Refresh();
                        axUtilCalls.ExportModel(model.ModelId.Value, exportFile, out context);
                        if (ProcessContextSuccesful(context))
                        {
                            LogInfo(string.Format("Successfully exported model id={0}, layer={1}, name={2}, version={3}, publisher={4} to \"{5}\"", model.ModelId.Value, model.Layer.Value, model.Name, model.Version, model.Publisher, exportFile), ExecutionStatus.Ok);
                        }
                    }
                }
            }
            else
            {
                LogInfo(string.Format("Folder does not exist: \"{0}\".", cbxModelDefaultExportPath.Text), ExecutionStatus.Error);
            }
        }

        private void btnDeleteSelectedModels_Click(object sender, EventArgs e)
        {
            List<ModelManifest> modelToDelete = new List<ModelManifest>();
            string modelList = string.Empty;
            foreach(ListViewItem item in lvwModels.Items)
            {
                if (!item.Checked)
                    continue;
                ModelManifest modelMan = item.Tag as ModelManifest;
                if (modelMan.ModelId.HasValue)
                {
                    if (modelMan.Layer.HasValue && modelMan.Layer.Value < (Layer)cbxAxLayer.SelectedItem)
                    {
                        if (!cbxModelDeletionPrevention.Checked)
                        {
                            switch (MessageBox.Show(
                                            string.Format("Are you sure you want to delete a model from layer: {0}?", modelMan.Layer.Value),
                                            "Low level model deletion",
                                            MessageBoxButtons.YesNoCancel,
                                            MessageBoxIcon.Warning))
                            {
                                case System.Windows.Forms.DialogResult.No:
                                    continue;
                                case System.Windows.Forms.DialogResult.Cancel:
                                    return;
                            }
                        }
                        else
                        {
                            switch (MessageBox.Show(
                                        string.Format("You have been restricted of deleting models on this layer({0}), do you wish to continue deleting other models?", modelMan.Layer.Value),
                                        "Model deletion prevention",
                                        MessageBoxButtons.YesNo,
                                        MessageBoxIcon.Error))
                            {
                                case DialogResult.Yes:
                                    continue;
                                case DialogResult.No:
                                    return;
                            }
                        }
                    }
                    modelToDelete.Add(modelMan);
                    modelList += string.Format("- id={0}, version={1}, name={2}\n", modelMan.ModelId.Value, modelMan.Version, modelMan.Name);
                }
            }

            if (MessageBox.Show(
                        string.Format("The following models will be deleted:\n{0}\nAre you sure?", modelList),
                        "Are you sure?",
                        MessageBoxButtons.YesNo,
                        MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Yes)
            {
                foreach (ModelManifest model in modelToDelete)
                {
                    AxUtilContext context;
                    LogInfo(string.Format("Deleting model id={0}, layer={1}, name={2}, version={3}, publisher={4}", model.ModelId.Value, model.Layer.Value, model.Name, model.Version, model.Publisher), ExecutionStatus.Ok);
                    axUtilCalls.DeleteModel(model.ModelId.Value, out context);
                    if (ProcessContextSuccesful(context))
                    {
                        LogInfo(string.Format("Successfully deleted model id={0}, layer={1}, name={2}, version={3}, publisher={4}", model.ModelId.Value, model.Layer.Value, model.Name, model.Version, model.Publisher), ExecutionStatus.Ok);
                    }
                }
            }
        }

        private void lblNewbieMode_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            FWizard fWizard = new FWizard();
            fWizard.ShowDialog();
        }

        private void cbxSqlServerName_SelectionChangeCommitted(object sender, EventArgs e)
        {
            RefreshDatabases();
        }

        private void RefreshDatabases()
        {
            if (!string.IsNullOrEmpty(cbxSqlServerName.Text))
            {
                databases = EnumerateSqlDatabases(cbxSqlServerName.Text);
                FilterDatabases();
            }
        }

        private void FilterDatabases()
        {
            List<string> filteredDatabases = new List<string>();
            filteredDatabases.AddRange(databases.ToArray());
            if (txtDbNameMask.Text != string.Empty)
            {
                filteredDatabases = databases.FindAll(
                                        delegate(string value)
                                        {
                                            return value.StartsWith(txtDbNameMask.Text);
                                        }
                                        );
            }
            cbxSqlDbName.Items.Clear();
            cbxSqlDbName.Items.AddRange(filteredDatabases.ToArray());
        }

        private void cbxSqlServerName_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                try
                {
                    RefreshDatabases();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(this, ex.Message, "Error retrieving databases", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void txtDbNameMask_TextChanged(object sender, EventArgs e)
        {
            FilterDatabases();
        }

        private void cbxSqlDbName_SelectionChangeCommitted(object sender, EventArgs e)
        {
        }

        private void cbxSqlDbName_SelectedValueChanged(object sender, EventArgs e)
        {
            axUtilCalls = new AxUtilCalls(cbxSqlServerName.Text, cbxSqlDbName.Text);
            btnListModels_Click(null, null);
        }

        private void btnAddModelFiles_Click(object sender, EventArgs e)
        {
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                foreach (string filename in openFileDlg.FileNames)
                {
                    AxUtilContext context;
                    LogInfo(string.Format("Reading model info from file {0}", filename), ExecutionStatus.Ok);
                    ModelContents content = axUtilCalls.ViewFile(filename, false, out context);
                    if (ProcessContextSuccesful(context))
                    {
                        LogInfo(string.Format("Successfully added file {0}", filename), ExecutionStatus.Ok);

                        if (content != null)
                        {
                            ModelImportInfo modelImportInfo = new ModelImportInfo(content, lvwImportModels.Items.Count+1, filename);
                            ListViewItem item = lvwImportModels.Items.Add(modelImportInfo.Order.ToString());
                            item.SubItems.AddRange(
                                new string[]
                                {
                                    content.Manifest.Layer.ToString(),
                                    content.Manifest.Name,
                                    content.Manifest.Publisher,
                                    content.Manifest.Version,
                                    content.Manifest.Signed.ToString(),
                                    content.Manifest.Description
                                });
                            item.Tag = modelImportInfo;
                        }
                    }
                }
                lvwImportModels.Sort();
            }
        }

        private void btnImportModelUp_Click(object sender, EventArgs e)
        {
            ListViewItem item = lvwImportModels.SelectedItems[0];
            ModelImportInfo info = item.Tag as ModelImportInfo;
            int oldOrder = info.Order;
            if (info.Order > 1)
            {
                --info.Order;
            }
            item.SubItems.RemoveAt(0);
            item.SubItems.Insert(0, new ListViewItem.ListViewSubItem(item, info.Order.ToString()));
            if (oldOrder > 1)
            {
                foreach (ListViewItem itm in lvwImportModels.Items)
                {
                    ModelImportInfo nfo = ((ModelImportInfo)itm.Tag);
                    if (nfo.Order == info.Order && item != itm)
                    {
                        ++nfo.Order;
                        itm.SubItems.RemoveAt(0);
                        itm.SubItems.Insert(0, new ListViewItem.ListViewSubItem(itm, nfo.Order.ToString()));
                    }
                }
            }
            lvwImportModels.Sort();
        }

        private void btnImportModelDown_Click(object sender, EventArgs e)
        {
            ListViewItem item = lvwImportModels.SelectedItems[0];
            ModelImportInfo info = item.Tag as ModelImportInfo;
            int oldOrder = info.Order;
            if (info.Order < lvwImportModels.Items.Count)
            {
                ++info.Order;
            }
            item.SubItems.RemoveAt(0);
            item.SubItems.Insert(0, new ListViewItem.ListViewSubItem(item, info.Order.ToString()));
            if (oldOrder < lvwImportModels.Items.Count)
            {
                foreach (ListViewItem itm in lvwImportModels.Items)
                {
                    ModelImportInfo nfo = ((ModelImportInfo)itm.Tag);
                    if (nfo.Order == info.Order && item != itm)
                    {
                        --nfo.Order;
                        itm.SubItems.RemoveAt(0);
                        itm.SubItems.Insert(0, new ListViewItem.ListViewSubItem(itm, nfo.Order.ToString()));
                    }
                }
            }
            lvwImportModels.Sort();
        }

        private void btnImportModelsClear_Click(object sender, EventArgs e)
        {
            lvwImportModels.Items.Clear();
        }

        private void btnImportModelFiles_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in lvwImportModels.Items)
            {
                ModelImportInfo info = item.Tag as ModelImportInfo;
                AxUtilContext context;
                axUtilCalls.ImportModel(info.Filename, cbxCreateParents.Checked, (ConflictModelResolverType)cbxModelConflicts.SelectedIndex, out context);
                if (ProcessContextSuccesful(context))
                {
                    LogInfo(string.Format("Successfully imported model file {0}", info.Filename), ExecutionStatus.Ok);
                }
                else
                {
                    LogInfo("Aborting operation...", ExecutionStatus.Error);
                    break;
                }
            }
        }

        private void pmuModelList_Opening(object sender, CancelEventArgs e)
        {
            pmuModelDetail.Enabled = lvwModels.SelectedItems.Count == 1;
        }

        private void pmuModelDetail_Click(object sender, EventArgs e)
        {
            ModelManifest model = lvwModels.SelectedItems[0].Tag as ModelManifest;
            AxUtilContext context;
            ModelContents content = axUtilCalls.ViewModel(model.ModelId.Value, true, out context);

            if (ProcessContextSuccesful(context))
            {
                ModelElements = content.Elements;
                lvwModelDetail.VirtualListSize = ModelElements.Count;
                tcModelActions.SelectedTab = tabModelDetail;
            }
        }

        private void cbxSqlDbName_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                axUtilCalls = new AxUtilCalls(cbxSqlServerName.Text, cbxSqlDbName.Text);
                btnListModels_Click(null, null);
            }
        }

        private void lvwImportModels_ColumnClick(object sender, ColumnClickEventArgs e)
        {
            // Determine whether the column is the same as the last column clicked.
            /*if (e.Column != sortColumn)
            {
                // Set the sort column to the new column.
                sortColumn = e.Column;
                // Set the sort order to ascending by default.
                lvwImportModels.Sorting = SortOrder.Ascending;
            }
            else*/
            {
                // Determine what the last sort order was and change it.
                if (lvwImportModels.Sorting == SortOrder.Ascending)
                    lvwImportModels.Sorting = SortOrder.Descending;
                else
                    lvwImportModels.Sorting = SortOrder.Ascending;
            }

            // Call the sort method to manually sort.
            lvwImportModels.Sort();
            // Set the ListViewItemSorter property to a new ListViewItemComparer
            // object.
            this.lvwImportModels.ListViewItemSorter = new ListViewItemComparer(sortColumn, lvwImportModels.Sorting);
        }

        private void FMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            Properties.Settings.Default.DatabaseFilter = txtDbNameMask.Text;
            Properties.Settings.Default.SqlServer = cbxSqlServerName.Text;
            Properties.Settings.Default.SqlModelDatabase = cbxSqlDbName.Text;
            Properties.Settings.Default.Save();
        }

        private void lvwModelDetail_RetrieveVirtualItem(object sender, RetrieveVirtualItemEventArgs e)
        {
            ListViewItem item = new ListViewItem();
            ModelElementInfo elemInfo = ModelElements[e.ItemIndex];
            item.Text = elemInfo.ElementType.ToString();
            item.SubItems.AddRange(new string[] { elemInfo.Name, elemInfo.Path });
            e.Item = item;
        }

        private void btnExportModelStore_Click(object sender, EventArgs e)
        {
            if (System.IO.Directory.Exists(cbxModelDefaultExportPath.Text))
            {
                string exportFile = string.Format(@"{0}\{1}_{2}.axmodelstore", cbxModelDefaultExportPath.Text.TrimEnd('\\'), axUtilCalls.SqlDbName, DateTime.Now.ToString("yyyyMMdd"));
                AxUtilContext context;
                LogInfo(string.Format("Exporting modelstore SQL={0}, DB={1} to \"{2}\"", axUtilCalls.SqlServerName, axUtilCalls.SqlDbName, exportFile), ExecutionStatus.Ok);
                rtbInfoLog.Refresh();
                axUtilCalls.ExportModelStore(exportFile, out context);
                if (ProcessContextSuccesful(context))
                {
                    LogInfo(string.Format("Successfully exported modelstore SQL={0}, DB={1} to \"{2}\"", axUtilCalls.SqlServerName, axUtilCalls.SqlDbName, exportFile), ExecutionStatus.Ok);
                }
            }
            else
            {
                LogInfo(string.Format("Folder does not exist: \"{0}\".", cbxModelDefaultExportPath.Text), ExecutionStatus.Error);
            }
        }

        private void RefreshLocalAosInfo()
        {
            if (tcAxUtil.SelectedTab == tabAosInfo && tabAosInfo.Tag == null)
            {
                List<AosDetails> aosDetails = AosDetails.GetAosDetailsFromRegistry(Environment.MachineName);
                lvwAos.Items.Clear();
                foreach (AosDetails aosDetail in aosDetails)
                {
                    ListViewItem item = lvwAos.Items.Add(aosDetail.AosInstance);
                    item.SubItems.AddRange(new string[] { aosDetail.AosVersion.ToString(), aosDetail.ActiveConfig, aosDetail.AosSqlServer, aosDetail.AosSqlDatabase, aosDetail.AosSqlModelDatabase });
                    item.Tag = aosDetail;
                }
            }
            ClientDetails clientDetails = ClientDetails.GetClientDetailsFromRegistry(Environment.MachineName);
            txtAxClientVersion.Text = clientDetails.Version;
            txtAxClientLocation.Text = clientDetails.Location;
            if (lvwAos.SelectedItems.Count == 0 && lvwAos.Items.Count > 0)
            {
                lvwAos.Items[0].Selected = true;
            }
        }

        private void tsmiShowModelstore_Click(object sender, EventArgs e)
        {
            if (lvwAos.SelectedItems.Count == 1)
            {
                ListViewItem item = lvwAos.SelectedItems[0];
                cbxSqlServerName.Text = item.SubItems[3].Text;
                cbxSqlDbName.Text = string.IsNullOrEmpty(item.SubItems[5].Text) ? item.SubItems[4].Text : item.SubItems[5].Text;
                tcAxUtil.SelectedTab = tabModelManagement;
                btnListModels_Click(btnListModels, null);
            }
        }

        private void btnAxClientGoTo_Click(object sender, EventArgs e)
        {
            if (System.IO.File.Exists(txtAxClientLocation.Text))
            {
                System.Diagnostics.Process.Start(System.IO.Path.GetDirectoryName(txtAxClientLocation.Text));
            }
        }

        private void tsmiGoToAOSFolder_Click(object sender, EventArgs e)
        {
            if (lvwAos.SelectedItems.Count == 1)
            {
                ListViewItem item = lvwAos.SelectedItems[0];
                AosDetails aosDetails = item.Tag as AosDetails;
                if (System.IO.File.Exists(aosDetails.AosPath))
                {
                    System.Diagnostics.Process.Start(System.IO.Path.GetDirectoryName(aosDetails.AosPath));
                }
            }
        }

        private List<AosServer> GetAosServers(string sqlServer, string sqlDatabase)
        {
            List<AosServer> aosServers = new List<AosServer>();
            using (System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(CreateConnectionString(sqlServer, sqlDatabase)))
            {
                SqlCon.Open();
                System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                SqlCom.Connection = SqlCon;
                SqlCom.CommandType = CommandType.Text;
                SqlCom.CommandText = @"select sc.SERVERID, sc.ENABLEBATCH, sss.SERVERID, count(scs.SESSIONID) clientcount from SYSSERVERCONFIG sc
join SYSSERVERSESSIONS sss
on
	sss.INSTANCE_NAME = substring(sc.SERVERID, 1, 2)
and	substring(sss.AOSID, 1, CHARINDEX('@', sss.AOSID)-1) = substring(sc.SERVERID, 4, len(sc.SERVERID)-3)
left outer join SYSCLIENTSESSIONS scs
on
	scs.SERVERID = sss.SERVERID
and	scs.STATUS = 1
group by sc.SERVERID, sc.ENABLEBATCH, sss.SERVERID";

                System.Data.SqlClient.SqlDataReader SqlDr = SqlCom.ExecuteReader();

                while (SqlDr.Read())
                {
                    AosServer aosServer = new AosServer();
                    string[] d = SqlDr.GetString(0).Split(new char[] { '@' });
                    aosServer.ServiceName = string.Format("AOS60${0}", d[0]);
                    aosServer.ServerName = d[1];
                    aosServer.EnableBatch = SqlDr.GetInt32(1) == 0 ? false : true;
                    aosServer.ServerId = SqlDr.GetInt32(2);
                    aosServer.ClientSessions = SqlDr.GetInt32(3);
                    aosServers.Add(aosServer);
                }
            }
            return aosServers;
        }

        private void tsmiGetEnvironmentDetails_Click(object sender, EventArgs e)
        {
            try
            {
                if (lvwAos.SelectedItems.Count == 1)
                {
                    ListViewItem item = lvwAos.SelectedItems[0];
                    AosDetails aosDetails = item.Tag as AosDetails;
                    List<AosServer> aosServers = GetAosServers(aosDetails.AosSqlServer, aosDetails.AosSqlDatabase);
                    lvwEnvAos.Items.Clear();
                    foreach (AosServer aosServer in aosServers)
                    {
                        ListViewItem itm = lvwEnvAos.Items.Add(aosServer.ServerName);
                        System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController(aosServer.ServiceName, aosServer.ServerName);
                        itm.SubItems.AddRange(new string[] { aosServer.ServerName, sc.Status.ToString(), sc.DisplayName, aosServer.ClientSessions.ToString(), aosServer.EnableBatch.ToString() });
                        itm.Tag = aosServer;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void tsmRefreshAosServices_Click(object sender, EventArgs e)
        {
            RefreshLocalAosInfo();
        }

        private void cmsEnvironment_Opening(object sender, CancelEventArgs e)
        {
            //e.Cancel = lvwEnvAos.SelectedItems.Count == 0;
            //if (!e.Cancel)
            {
                int started = 0;
                int stopped = 0;
                foreach (ListViewItem item in lvwEnvAos.SelectedItems)
                {
                    AosServer aosServer = item.Tag as AosServer;
                    System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController(aosServer.ServiceName, aosServer.ServerName);
                    switch(sc.Status)
                    {
                        case System.ServiceProcess.ServiceControllerStatus.Running: started++; break;
                        case System.ServiceProcess.ServiceControllerStatus.Stopped: stopped++; break;
                    }
                }
                tsmiStartService.Enabled = started == 0 && stopped > 0;
                tsmiStopService.Enabled = started > 0 && stopped == 0;
                tsmiRefreshEnvironment.Enabled = lvwAos.SelectedItems.Count == 1;
            }
        }

        private void tsmiRefreshEnvironment_Click(object sender, EventArgs e)
        {
            tsmiGetEnvironmentDetails_Click(tsmiGetEnvironmentDetails, null);
        }

        private void tsmiStartService_Click(object sender, EventArgs e)
        {
            if (lvwEnvAos.SelectedItems.Count > 0)
            {
                foreach (ListViewItem item in lvwEnvAos.SelectedItems)
                {
                    AosServer aosServer = item.Tag as AosServer;
                    System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController(aosServer.ServiceName, aosServer.ServerName);
                    if (sc.Status == System.ServiceProcess.ServiceControllerStatus.Stopped)
                    {
                        try
                        {
                            sc.Start();
                            tsmiGetEnvironmentDetails_Click(tsmiGetEnvironmentDetails, null);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
        }

        private void tsmiStopService_Click(object sender, EventArgs e)
        {
            if (lvwEnvAos.SelectedItems.Count > 0)
            {
                foreach (ListViewItem item in lvwEnvAos.SelectedItems)
                {
                    AosServer aosServer = item.Tag as AosServer;
                    System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController(aosServer.ServiceName, aosServer.ServerName);
                    if (sc.Status == System.ServiceProcess.ServiceControllerStatus.Running)
                    {
                        try
                        {
                            sc.Stop();
                            tsmiGetEnvironmentDetails_Click(tsmiGetEnvironmentDetails, null);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
        }


        public void QueryRemoteComputer(string server)
        {
            string queryString = "*[System[TimeCreated[timediff(@SystemTime) <= 3600000]]]"; // XPATH Query
            System.Diagnostics.Eventing.Reader.EventLogSession session = new System.Diagnostics.Eventing.Reader.EventLogSession(server);

            // Query the Application log on the remote computer.
            System.Diagnostics.Eventing.Reader.EventLogQuery query = new System.Diagnostics.Eventing.Reader.EventLogQuery("Application", System.Diagnostics.Eventing.Reader.PathType.LogName, queryString);
            query.Session = session;
            query.ReverseDirection = true;
            try
            {
                System.Diagnostics.Eventing.Reader.EventLogReader logReader = new System.Diagnostics.Eventing.Reader.EventLogReader(query);
                lvwEventLog.Items.Clear();
                for (System.Diagnostics.Eventing.Reader.EventRecord eventInstance = logReader.ReadEvent(); null != eventInstance; eventInstance = logReader.ReadEvent())
                {
                    int level = eventInstance.Level.HasValue ? eventInstance.Level.Value : 0;
                    string levelStr = string.Empty;
                    int imgIdx = 0;
                    switch (level)
                    {
                        case 1: levelStr = "Critical"; imgIdx = 0; break;
                        case 2: levelStr = "Error"; imgIdx = 0; break;
                        case 3: levelStr = "Warning"; imgIdx = 1; break;
                        case 4: levelStr = "Information"; imgIdx = 2; break;
                        default: levelStr = level.ToString(); break;
                    }
                    ListViewItem item = lvwEventLog.Items.Add(levelStr);
                    item.ImageIndex = imgIdx;
                    item.StateImageIndex = imgIdx;
                    /*Console.WriteLine("-----------------------------------------------------");
                    Console.WriteLine("Event ID: {0}", eventInstance.Id);
                    Console.WriteLine("Publisher: {0}", eventInstance.ProviderName);*/

                    string detail = string.Empty;
                    try
                    {
                        detail = eventInstance.FormatDescription();
                    }
                    catch (System.Diagnostics.Eventing.Reader.EventLogException)
                    {
                        // The event description contains parameters, and no parameters were 
                        // passed to the FormatDescription method, so an exception is thrown.

                    }

                    // Cast the EventRecord object as an EventLogRecord object to 
                    // access the EventLogRecord class properties
                    System.Diagnostics.Eventing.Reader.EventLogRecord logRecord = (System.Diagnostics.Eventing.Reader.EventLogRecord)eventInstance;
                    //Console.WriteLine("Container Event Log: {0}", logRecord.ContainerLog);
                    if (string.IsNullOrWhiteSpace(detail))
                    {
                        foreach (System.Diagnostics.Eventing.Reader.EventProperty line in logRecord.Properties)
                        {
                            if (line.Value is string)
                            detail += string.Format(" {0}", (string)line.Value);
                        }
                    }
                    item.SubItems.AddRange(new string[] { eventInstance.TimeCreated.ToString(), eventInstance.ProviderName, eventInstance.Id.ToString(), detail });
                }
            }
            catch (System.Diagnostics.Eventing.Reader.EventLogException e)
            {
                MessageBox.Show("Could not query the remote computer! " + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
        }

        private void btnRefreshEventLog_Click(object sender, EventArgs e)
        {
            if (lvwEnvAos.SelectedItems.Count == 1)
            {
                ListViewItem item = lvwEnvAos.SelectedItems[0];
                QueryRemoteComputer(item.SubItems[1].Text);
            }
        }

        private void lvwAos_SelectedIndexChanged(object sender, EventArgs e)
        {
            tsmiGetEnvironmentDetails_Click(tsmiGetEnvironmentDetails, null);
        }

        private void lvwEnvAos_SelectedIndexChanged(object sender, EventArgs e)
        {
            btnRefreshEventLog_Click(btnRefreshEventLog, null);
        }

        private void btnOpenEventViewer_Click(object sender, EventArgs e)
        {
            if (lvwEnvAos.SelectedItems.Count > 0)
            {
                foreach (ListViewItem item in lvwEnvAos.SelectedItems)
                {
                    System.Diagnostics.Process myprocess = new System.Diagnostics.Process();
                    myprocess.StartInfo.CreateNoWindow = true;
                    myprocess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
                    myprocess.StartInfo.UseShellExecute = false;
                    //myprocess.StartInfo.Verb = "runas";
                    myprocess.StartInfo.FileName = "cmd.exe";
                    //myprocess.StartInfo.UserName = "administrator";
                    //myprocess.StartInfo.Password = System.Security.MakeSecureString("123456");
                    myprocess.StartInfo.Arguments = string.Format("/k mmc.exe eventvwr.msc /computer:{0}", item.SubItems[1].Text);
                    myprocess.Start();
                    myprocess.Close();
                }
            }
        }

        private void tsmiEnableBatchProcessing_Click(object sender, EventArgs e)
        {

        }

        private void tsmiDisableBatchProcessing_Click(object sender, EventArgs e)
        {

        }
    }
}
